package example.service;

import example.dao.connect;
import org.json.JSONException;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class goods_c {

    public static String page_t() throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        String sql = "select count(*) as pagea from goods";
        ResultSet p = stmt.executeQuery(sql);
        return jsonzhuan.resultSetToJson(p);
    }

    public static String page_t1() throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        String sql = "select count(*) as pagea from goods where substring(goodsid,1,1) = 'A' and state = '1' ";
        ResultSet p = stmt.executeQuery(sql);
        return jsonzhuan.resultSetToJson(p);
    }

    public static String page_t2() throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        String sql = "select count(*) as pagea from goods where substring(goodsid,1,1) = 'B' and state = '1' ";
        ResultSet p = stmt.executeQuery(sql);
        return jsonzhuan.resultSetToJson(p);
    }

    public static String page_t3() throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        String sql = "select count(*) as pagea from goods where substring(goodsid,1,1) = 'C' and state = '1' ";
        ResultSet p = stmt.executeQuery(sql);
        return jsonzhuan.resultSetToJson(p);
    }

    public static String first1(String page) throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        String sql = null;
        if(page.equals("1")){
            sql = "SELECT * FROM goods WHERE substring(goodsid,1,1) = 'A' and state = '1' LIMIT 0,12" ;
        }
        else {
            int over = (Integer.parseInt(page)-1) * 12;
            sql = "SELECT * FROM goods WHERE substring(goodsid,1,1) = 'A' LIMIT "+over+",12";
        }
        ResultSet p = stmt.executeQuery(sql);
        return jsonzhuan.resultSetToJson(p);
    }

    public static String first(String page) throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        String sql = null;
        if(page.equals("1")){
            sql = "SELECT * FROM goods WHERE state = '1'  LIMIT 0,12";
        }
        else {
            int over = (Integer.parseInt(page)-1) * 12;
            sql = "SELECT * FROM goods  LIMIT "+over+",12";
        }
        ResultSet p = stmt.executeQuery(sql);
        return jsonzhuan.resultSetToJson(p);
    }

    public static String first2(String page) throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        String sql = null;
        if(page.equals("1")){
            sql = "SELECT * FROM goods WHERE substring(goodsid,1,1) = 'B' and state = '1'  LIMIT 0,12";
        }
        else {
            int over = (Integer.parseInt(page)-1) * 12;
            sql = "SELECT * FROM goods WHERE substring(goodsid,1,1) = 'B'  and state = '1'  order by goodsid LIMIT "+over+",12";
        }
        ResultSet p = stmt.executeQuery(sql);
        return jsonzhuan.resultSetToJson(p);
    }

    public static String first3(String page) throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        String sql = null;
        if(page.equals("1")){
            sql = "SELECT * FROM goods WHERE substring(goodsid,1,1) = 'C' and state = '1' LIMIT 0,12";
        }
        else {
            int over = (Integer.parseInt(page)-1) * 12;
            sql = "SELECT * FROM goods WHERE substring(goodsid,1,1) = 'C'  and state = '1'  order by goodsid LIMIT "+over+",12";
        }
        ResultSet p = stmt.executeQuery(sql);
        return jsonzhuan.resultSetToJson(p);
    }

    public static String insert(String number,String username,String goodsid) {
        try
        {
            Connection conn = connect.getConnection();
            String sql="insert into sale ( username , goodsid , state , number1 ) values('"+username+"','"+goodsid+"','1','"+number+"')";
            Statement stmt=conn.createStatement();//创建一个Statement对象
            stmt.executeUpdate(sql);//执行sql语句
            return "1";
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            System.out.println("数据库连接出错");
            return "2";
        }
    }
}
